import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllTrain():
    print('return all train')
    sql = "SELECT train.train_id, train.train_type, train.line_id, l.line_name FROM train left join line as l on " \
          "train.line_id=l.line_id "
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有车")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllLine():
    sql = "SELECT line_id, line_name FROM line"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有线")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def newtrain(id, type):
    print('insert a new train')

    number = getnewnumber(id)
    sql = "insert into train values( '" + number + "', '" + id + "','" + type + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber(id):
    sql = "select max(train_id) from train where line_id='" + id + "'"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    if data[0] is None:
        str = id+'001'
    else:
        number = int(data[0]) + 1
        print(number)
        str = "%05d" % number

    print(str)
    return str


def deleteTrain(word):
    sql = "Delete FROM train WHERE train_id =" + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchTrain(word):
    sql = "SELECT train.train_id, train.train_type, train.line_id, l.line_name FROM train left join line as l on " \
          "train.line_id=l.line_id where train.train_id LIKE '%" + word + "%' or train.train_type LIKE '%" + word + "%' or " \
          "train.line_id LIKE '%" + word + "%' or l.line_name LIKE '%" + word + "%' "
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的车辆")
        return 0
    else:
        print("搜索成功")
        return temp
